package cn.you.GenghisKhan.common.jdbc;

import cn.you.GenghisKhan.common.utils.ConvertUtil;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import org.springframework.util.LinkedCaseInsensitiveMap;

import java.sql.*;
import java.util.*;

public class JDBCMySql {
    private String driver = "com.mysql.jdbc.Driver";

    private String url;

    public JDBCMySql(String url) {

        this.url = url;
    }


    public Connection getConnection() throws SQLException,
            ClassNotFoundException {
        Connection conn = null;
        Class.forName(driver);
        // 加载连接

        conn = (Connection) DriverManager.getConnection(url);

        return conn;
    }

    public Object GetFristData(String sql) throws ClassNotFoundException,
            SQLException {
        ResultSet rs = null;
        Object o = null;
        Connection conn = null;
        CallableStatement stat = null;
        try {
            conn = getConnection();
            stat = conn.prepareCall(sql);
            boolean hadResults = stat.execute();

            rs = stat.getResultSet();

            ResultSetMetaData data = rs.getMetaData();
            int fieldcount = data.getColumnCount();
            if (fieldcount > 0) {

                while (rs != null && rs.next()) {

                    o = rs.getObject(1);

                    break;
                }
            }
        } finally {
            this.jdbcClose(rs, conn, stat);
        }
        return o;
    }

    /**
     * 获取rds数据
     *
     * @param sql
     * @return
     * @throws Exception public DataSet ExecResultsetDataSet(String sql) throws Exception {
     *                   ResultSet rs = null;
     *                   DataSet myset = new DataSet();
     *                   Connection conn = null;
     *                   CallableStatement stat = null;
     *                   try {
     *                   conn = getConnection();
     *                   stat = conn.prepareCall(sql);
     *                   boolean hadResults = stat.execute();
     *                   while (hadResults) {
     *                   rs = stat.getResultSet();
     *                   DataTable mytb = new DataTable();
     *                   ResultSetMetaData data = rs.getMetaData();
     *                   <p>
     *                   List<DataType> columnTypes = new ArrayList<DataType>();
     *                   <p>
     *                   int fieldcount = data.getColumnCount();
     *                   for (int fieldIndex = 1; fieldIndex <= fieldcount; fieldIndex++) {
     *                   DataType type = Record.TypeToDataType(data
     *                   .getColumnTypeName(fieldIndex));
     *                   String columnname = data.getColumnLabel(fieldIndex);
     *                   columnname = columnname.equals("") ? "field" + fieldIndex
     *                   : columnname;
     *                   columnTypes.add(type);
     *                   mytb.Columns.Add(columnname, String.class);
     *                   }
     *                   while (rs != null && rs.next()) {
     *                   Object[] values = new Object[fieldcount];
     *                   for (int i = 1; i <= fieldcount; i++) {
     *                   <p>
     *                   Object columnValue;
     *                   DataType dp = columnTypes.get(i - 1);
     *                   if (DataType.Int16 == dp) {
     *                   columnValue = rs.getInt(i);
     *                   <p>
     *                   } else {
     *                   columnValue = rs.getObject(i);
     *                   }
     *                   if (rs.wasNull()) {
     *                   columnValue = null;
     *                   }
     *                   values[i - 1] = columnValue;
     *                   }
     *                   mytb.Rows.Add(values);
     *                   }
     *                   hadResults = stat.getMoreResults();
     *                   myset.Tables.add(mytb);
     *                   }
     *                   } catch (Exception e) {
     *                   throw e;
     *                   } finally {
     *                   this.jdbcClose(rs, conn, stat);
     *                   }
     *                   return myset;
     *                   }
     */

    public List<Map<String, Object>> getListData(String sql) throws SQLException,
            ClassNotFoundException {
        List returnList = new ArrayList();
        ResultSet rs = null;
        Object o = null;
        Connection conn = null;
        CallableStatement stat = null;
        try {
            conn = getConnection();
            stat = conn.prepareCall(sql);
            boolean hadResults = stat.execute();
            rs = stat.getResultSet();
            if (rs == null)
                return Collections.EMPTY_LIST;
            ResultSetMetaData data = rs.getMetaData();
            int fieldcount = data.getColumnCount();
            if (fieldcount > 0) {
                Map<String, Object> rowData = new HashMap();
                while (rs.next()) {
                    rowData = new HashMap(fieldcount);
                    for (int i = 1; i <= fieldcount; i++) {
                        rowData.put(data.getColumnName(i), rs.getObject(i));
                    }
                    returnList.add(rowData);
                }

            }
        } finally {
            this.jdbcClose(rs, conn, stat);
        }
        return returnList;
    }

    public Map<String, Object> getDate(String sql) throws SQLException,
            ClassNotFoundException {
        Map<String, Object> rowData =new LinkedCaseInsensitiveMap();

        ResultSet rs = null;
        Object o = null;
        Connection conn = null;
        CallableStatement stat = null;
        try {
            conn = getConnection();
            stat = conn.prepareCall(sql);
            boolean hadResults = stat.execute();
            rs = stat.getResultSet();
            if (rs == null)
                return null;
            ResultSetMetaData data = rs.getMetaData();

            int fieldcount = data.getColumnCount();
            if (fieldcount > 0) {

                while (rs.next()) {
                    rowData = new HashMap(fieldcount);
                    for (int i = 1; i <= fieldcount; i++) {
                          rowData.put(data.getColumnLabel(i), rs.getObject(i));
                    }
                    break;
                }

            }
        } finally {
            this.jdbcClose(rs, conn, stat);
        }
        return rowData;
    }

    public <T> List<T> getObjectList(Class<T> clazz, String sql, Map<String, String> filedMapping) throws Exception {
        Map<String, Object> mp = new HashMap<String, Object>();
        List<Map<String, Object>> list = getListData(sql);
        List<T> needList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map objMap = list.get(i);
            mp = convertMap(filedMapping, objMap);
            T t = ConvertUtil.map2Bean(mp, clazz);
            needList.add(t);
        }
        return needList;
    }
    public <T> List<T> getObjectList(Class<T> clazz, String sql) throws Exception {

        List<Map<String, Object>> list = getListData(sql);
        List<T> needList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map<String,Object> objMap = list.get(i);
            T t = ConvertUtil.map2Bean(objMap, clazz);
            needList.add(t);
        }
        return needList;
    }
    public <T> T getObject(Class<T> clazz, String sql, Map<String, String> filedMapping) throws Exception {
        Map<String, Object> mp = new HashMap<String, Object>();
        Map<String, Object> objMap = getDate(sql);
        List<T> needList = new ArrayList<>();
        mp = convertMap(filedMapping, objMap);
        T t = ConvertUtil.map2Bean(mp, clazz);
        return t;
    }
    public <T> T getObject(Class<T> clazz, String sql) throws Exception {
        Map<String, Object> mp = getDate(sql);
        T t = ConvertUtil.map2Bean(mp, clazz);
        return t;
    }
    private <T> Map<String, T> convertMap(Map<String, String> filedMapping, Map<String, T> objs) {
        Map<String, T> map = new HashMap<>();
        Iterator<Map.Entry<String, String>> entries = filedMapping.entrySet().iterator();

        while (entries.hasNext()) {
            Map.Entry<String, String> entry = entries.next();

            if (objs.containsKey(entry.getValue())) {
                String key = entry.getKey();
                String dbkey = entry.getValue();
                T t = objs.get(dbkey);
                map.put(key, t);
            }
        }
        return map;
    }


    public Object SGetFristData(String sql) throws ClassNotFoundException,
            SQLException {
        ResultSet rs = null;
        Object o = null;
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection();
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData data = rs.getMetaData();
            int fieldcount = data.getColumnCount();
            if (fieldcount > 0) {
                while (rs != null && rs.next()) {
                    o = rs.getObject(1);
                    break;
                }
            }
            // 提交事务
            conn.commit();
            conn.setAutoCommit(autoCommit);
        } finally {
            this.jdbcClose(rs, conn, stmt);
        }
        return o;
    }


    private void jdbcClose(ResultSet rs, Connection conn, Statement stat) throws SQLException {
        if (null != rs) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                throw e;
            }
        }
        if (null != stat) {
            try {
                stat.close();
                stat = null;
            } catch (SQLException e) {
                throw e;
            }
        }
        if (null != conn) {
            try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
                throw e;
            }
        }
    }

    //执行批量的插入操作
    public void sqlBatch(String sql, Set<Object[]> paramList) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            if (paramList.size() > 0) {
                conn = getConnection();
                conn.setAutoCommit(false);
                pst = (PreparedStatement) conn.prepareStatement(sql);

                int i = 1;
                for (Iterator iterator = paramList.iterator(); iterator
                        .hasNext(); ) {
                    Object[] param = (Object[]) iterator.next();
                    for (int j = 1; j <= param.length; j++) {
                        pst.setObject(j, param[j - 1]);
                    }
                    pst.addBatch();

                    if (i % 1000 == 0 || i == paramList.size()) {
                        pst.executeBatch();
                        conn.commit();
                        pst.clearBatch();
                    }

                    i++;
                }
            }
        } catch (SQLException e) {
            conn.rollback();
            throw new SQLException(e);
        } finally {
            jdbcClose(null, conn, pst);
        }
    }

}
